In [1]:
import pymysql
db = pymysql.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"world",
charset='utf8',
)
df = pd.read_sql("SELECT * FROM Country;", db)
In [2]:
#cursor
In [3]:
cursor = db.cursor()
In [5]:
# 1. 실제로 명령을 수행하는 부분 - 서버
cursor.execute("SELECT * FROM Country;")
Out[5]:
In [7]:
# 2. 데이터를 가져오는 부분 - 서버 => 클라이언트
cursor.fetchall() #결과를 불러 온다. Pandas는 사실 이걸 읽는 것이다.
Out[7]:
In [8]:
pd.read_sql("SELECT * FROM Country;", db)
Out[8]:
In [ ]:
In [10]:
country_df = pd.read_sql("SELECT * FROM Country;", db)
In [13]:
country_df[country_df["GovernmentForm"].str.contains("Republic")].head(2)
Out[13]:
In [14]:
country_df[country_df["GovernmentForm"].str.startswith("Republic")].head(2)
Out[14]:
In [15]:
country_df[country_df["GovernmentForm"].str.endswith("Republic")].head(2)
Out[15]:
In [17]:
gf_str = country_df["GovernmentForm"].str
In [18]:
gf_str. #이렇게 해서 str에 어떤 기능들이 있는 지 확인할 수 있다.
In [19]:
# SQL을 이용
SQL_QUERY = """
SELECT Name, GovernmentForm
From Country
WHERE
GovernmentForm LIKE "Republic"
;
"""
pd.read_sql(SQL_QUERY, db).head()
Out[19]:
In [22]:
SQL_QUERY = """
SELECT Name, GovernmentForm
From Country
WHERE
GovernmentForm LIKE "%Republic%"
;
"""
pd.read_sql(SQL_QUERY, db)
Out[22]:
In [24]:
db = pymysql.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"sakila",
charset = "utf8",
)
In [26]:
customer_df = pd.read_sql("SELECT * FROM customer;", db)
In [28]:
address_df = pd.read_sql("SELECT * FROM address;", db)
In [29]:
customer_df.columns
Out[29]:
In [30]:
address_df.columns
Out[30]:
In [32]:
customer_df.merge(address_df, on="address_id")[["first_name", "last_name", "address"]]
# left_on="address_id"
# right_on="address_id"
# => on...
Out[32]:
In [34]:
SQL_QUERY = """
SELECT COUNT(*)
FROM customer
;
"""
pd.read_sql(SQL_QUERY, db).head()
Out[34]:
In [35]:
SQL_QUERY = """
SELECT COUNT(*)
FROM address
;
"""
pd.read_sql(SQL_QUERY, db).head()
Out[35]:
In [37]:
SQL_QUERY = """
SELECT COUNT(*)
FROM customer, address
;
"""
pd.read_sql(SQL_QUERY, db).head()
Out[37]:
In [40]:
SQL_QUERY = """
SELECT customer.first_name, customer.last_name, address.address
FROM customer, address
WHERE
customer.address_id = address.address_id
;
"""
df = pd.read_sql(SQL_QUERY, db).head()
In [43]:
SQL_QUERY = """
SELECT customer.first_name, customer.last_name, address.address
FROM customer
JOIN address ON customer.address_id = address.address_id
;
"""
pd.read_sql(SQL_QUERY, db).head()
Out[43]:
In [52]:
import time
start_time = time.time()
customer_df = pd.read_sql("SELECT * FROM customer;", db)
address_df = pd.read_sql("SELECT * FROM address;", db)
df = customer_df.merge(address_df, on="address_id")
end_time = time.time()
exec_time = end_time - start_time
print(exec_time)
In [64]:
start_time = time.time()
SQL_QUERY = """
SELECT customer.first_name, customer.last_name, address.address
FROM customer, address
WHERE
customer.address_id = address.address_id
;
"""
df = pd.read_sql(SQL_QUERY, db)
end_time = time.time()
exec_time = end_time - start_time
print(exec_time)
In [63]:
start_time = time.time()
SQL_QUERY = """
SELECT customer.first_name, customer.last_name, address.address
FROM customer
JOIN address ON customer.address_id = address.address_id
;
"""
pd.read_sql(SQL_QUERY, db)
end_time = time.time()
exec_time = end_time - start_time
print(exec_time)
In [65]:
db = pymysql.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"world",
charset='utf8',
)
country_df = pd.read_sql("SELECT * FROM Country;", db)
city_df = pd.read_sql("SELECT * FROM City;", db)
In [66]:
country_df.columns
Out[66]:
In [67]:
city_df.columns
Out[67]:
In [73]:
city_df.merge(country_df, right_on="Code", left_on="CountryCode")[["Name_x", "Name_y"]]
Out[73]:
In [77]:
SQL_QUERY = """
SELECT Country.Name "Country Name", City.Name "City Name"
FROM Country, City
WHERE Country.Code = City.CountryCode
;
"""
pd.read_sql(SQL_QUERY, db).head(2)
Out[77]:
In [78]:
SQL_QUERY = """
SELECT co.Name "Country Name", ci.Name "City Name"
FROM Country co, City ci
WHERE co.Code = ci.CountryCode
;
"""
pd.read_sql(SQL_QUERY, db).head(2)
Out[78]:
In [80]:
SQL_QUERY = """
SELECT co.Name "Country Name", ci.Name "City Name"
FROM Country co
JOIN City ci
ON co.code = ci.CountryCode
;
"""
pd.read_sql(SQL_QUERY, db).head(2)
Out[80]: